Enterprise applications like customer relationship management (CRM) applications manage information stored in relational databases. Users of enterprise applications, such as management, salespeople, people providing customer service, customers, etc., modify information managed by the applications. The present invention will be described with reference to CRM applications, it being understood the present invention should not be limited thereto.
In a relational database, information is typically stored in tables, which can be seen as two-dimensional structures of columns and rows. In more traditional computer terminology, the columns are called fields and the rows are called records. Each record of a table represents one object (e.g., a person), event, or relationship. All records within a table represent the same type of object. Tables in a relational database may contain hundreds or thousands of records.
A field in a record contains one value of particular type of information. A field should not contain one type of information for one record in a table and another type of information for another record in the same table. Each field has a name and an information type. Essentially, there are three main information types: text, numbers, and dates. Some fields allow nulls, which are unknown values. Other fields do not allow them. If a field does not allow nulls, then a value is usually required in the field for every record of the table.
Each field has a position within the table. That is, the fields are an ordered set. This contrasts with records, which have no fixed order. Information about the fields—their names, information types, positions, and whether they accept nulls—is all considered to be part of the definition of the table itself. In contrast, information about the records is considered to be part of the data and not part of the definition of the table.
Primary and foreign keys are important components in relational database tables. Most tables in relational databases contain a primary key that uniquely identifies each row or record. Each row must have its own identity, so no two rows are allowed to have the same primary key. Foreign keys are contained in a column of a child table and reference primary keys in a parent table. A primary key in a parent table can be referenced in many child table records. This is called a one-to-many (1:M) relationship. Many-to-many (M:M) relationships may also occur in relational databases. Each record in a pair of tables can relate to a number of records in the other table. M:M relationships require a third table, known as an intersection table, because relational databases can't directly accommodate the M;M relationship.
Structured query language (SQL) is a computer language designed to access information stored in relational databases. Enterprise applications such as CRM can generate SQL statements for accessing information in tables. The select statement or command can used to retrieve information from a table. The basic select statement has four clauses that include: the select clause, which identifies which columns are sought; the from clause, which identifies a table that contains the data sought; the where clause, which identifies the rows sought; and the order by clause, which identifies how to sort the final result. The results of a select statement are typically returned in a result table, which has columns and rows, and which can be displayed on a monitor of, for example, a client computer system that is in data communication with a database system via an enterprise application.
Tables can be modified by adding new records, updating values in one or more fields of existing records, or deleting records entirely. Records can be added to a table using an insert statement, which typically begins with insert into, followed by the name of the table. The insert statement typically has the word values followed by a list of values to be inserted into respective fields of the new record. The value put into any field of a record must always match the information type of that field: text, number, or date. Values within one or more columns of a table can be modified using the update statement. The syntax of update statements is typically easier to read and work with when compared to the insert statement. In update statements, the name of the field is aligned with its new value. A record in a table may be removed using the delete statement. As will be more fully described below, a record can be “soft deleted” via an update statement. A soft deleted record is not really removed from the database. Rather, the soft deleted record appears to have been removed from the database.
The use of the same reference symbols in different drawings indicates similar or identical items.